//---------------------------------------------------------------------
// <copyright file="DmlSqlGenerator.cs" company="Microsoft">
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//---------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.Text;
using System.Data.Metadata.Edm;
using System.Data.Common.CommandTrees;
using System.Data.Common.Utils;
using System.Data;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;

namespace EFOracleOdpProvider.SqlGen
{
    /// <summary>
    /// Class generating SQL for a DML command tree.
    /// </summary>
    internal static class DmlSqlGenerator
    {
        private const int s_commandTextBuilderInitialCapacity = 256;

        internal static string GenerateUpdateSql(DbUpdateCommandTree tree, EFOracleOdpProviderManifest providerManifest, EFOracleVersion sqlVersion, out List<OracleParameter> parameters)
        {
            const string dummySetParameter = "@p";

            StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
            ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlVersion);

            if (tree.SetClauses.Count == 0)
            {
                commandText.AppendLine("declare " + dummySetParameter + " int");
            }

            // update [schemaName].[tableName]
            commandText.Append("update ");
            tree.Target.Expression.Accept(translator);
            commandText.AppendLine();

            // set c1 = ..., c2 = ..., ...
            bool first = true;
            commandText.Append("set ");
            foreach (DbSetClause setClause in tree.SetClauses)
            {
                if (first) { first = false; }
                else { commandText.Append(", "); }
                setClause.Property.Accept(translator);
                commandText.Append(" = ");
                setClause.Value.Accept(translator);
            }

            if (first)
            {
                // If first is still true, it indicates there were no set
                // clauses. Introduce a fake set clause so that:
                // - we acquire the appropriate locks
                // - server-gen columns (e.g. timestamp) get recomputed
                //
                // We use the following pattern:
                //
                //  update Foo
                //  set @p = 0
                //  where ...
                commandText.Append(dummySetParameter + " = 0");
            }
            commandText.AppendLine();

            // where c1 = ..., c2 = ...
            commandText.Append("where ");
            tree.Predicate.Accept(translator);
            commandText.AppendLine();

            // generate returning sql
            GenerateReturningSql(commandText, tree, translator, tree.Returning, providerManifest, sqlVersion); 

            parameters = translator.Parameters;
            return commandText.ToString();
        }

        internal static string GenerateDeleteSql(DbDeleteCommandTree tree, EFOracleOdpProviderManifest providerManifest, EFOracleVersion sqlVersion, out List<OracleParameter> parameters)
        {
            StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
            ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, false, sqlVersion);

            // delete [schemaName].[tableName]
            commandText.Append("delete ");
            tree.Target.Expression.Accept(translator);
            commandText.AppendLine();
            
            // where c1 = ... AND c2 = ...
            commandText.Append("where ");
            tree.Predicate.Accept(translator);

            parameters = translator.Parameters;
            return commandText.ToString();
        }

        internal static string GenerateInsertSql(DbInsertCommandTree tree, EFOracleOdpProviderManifest providerManifest, EFOracleVersion sqlVersion, out List<OracleParameter> parameters)
        {
            StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
            ExpressionTranslator translator = new ExpressionTranslator(commandText, tree,
                null != tree.Returning, sqlVersion);

            // insert [schemaName].[tableName]
            commandText.Append("insert into ");
            tree.Target.Expression.Accept(translator);

            if (0 < tree.SetClauses.Count)
            {
                // (c1, c2, c3, ...)
                commandText.Append("(");
                bool first = true;
                foreach (DbSetClause setClause in tree.SetClauses)
                {
                    if (first) { first = false; }
                    else { commandText.Append(", "); }
                    setClause.Property.Accept(translator);
                }
                commandText.AppendLine(")");

                // values c1, c2, ...
                first = true;
                commandText.Append("values (");
                foreach (DbSetClause setClause in tree.SetClauses)
                {
                    if (first) { first = false; }
                    else { commandText.Append(", "); }
                    setClause.Value.Accept(translator);

                    translator.RegisterMemberValue(setClause.Property, setClause.Value);
                }
                commandText.AppendLine(")");
            }
            else
            {
                // default values
                commandText.AppendLine().AppendLine("default values");
            }
            // generate returning sql
            GenerateReturningSql(commandText, tree, translator, tree.Returning, providerManifest, sqlVersion); 

            parameters = translator.Parameters;
            return commandText.ToString();
        }

        // Generates T-SQL describing a member
        // Requires: member must belong to an entity type (a safe requirement for DML
        // SQL gen, where we only access table columns)
        private static string GenerateMemberTSql(EdmMember member)
        {
            return SqlGenerator.QuoteIdentifier(member.Name);
        }

        /// <summary>
        /// Generates SQL fragment returning server-generated values.
        /// Requires: translator knows about member values so that we can figure out
        /// how to construct the key predicate.
        /// <code>
        /// Sample SQL:
        ///     
        ///     select IdentityValue
        ///     from dbo.MyTable
        ///     where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
        /// 
        /// or
        /// 
        ///     select TimestamptValue
        ///     from dbo.MyTable
        ///     where @@ROWCOUNT > 0 and Id = 1
        /// 
        /// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
        /// </code>
        /// </summary>
        /// <param name="commandText">Builder containing command text</param>
        /// <param name="tree">Modification command tree</param>
        /// <param name="translator">Translator used to produce DML SQL statement
        /// for the tree</param>
        /// <param name="returning">Returning expression. If null, the method returns
        /// immediately without producing a SELECT statement.</param>
        private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
            ExpressionTranslator translator, DbExpression returning, EFOracleOdpProviderManifest providerManifest,
            EFOracleVersion sqlVersion)
        {
            // Nothing to do if there is no Returning expression
            if (null == returning) { return; }

#if SQLSERVER
            // select
            commandText.Append("select ");
            returning.Accept(translator);
            commandText.AppendLine();

            // from
            commandText.Append("from ");
            tree.Target.Expression.Accept(translator);
            commandText.AppendLine();

            // where
            commandText.Append("where @@ROWCOUNT > 0");
            EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
            bool identity = false;
            foreach (EdmMember keyMember in table.ElementType.KeyMembers)
            {
                commandText.Append(" and ");
                commandText.Append(GenerateMemberTSql(keyMember));
                commandText.Append(" = ");

                // retrieve member value sql. the translator remembers member values
                // as it constructs the DML statement (which precedes the "returning"
                // SQL)
                SqlParameter value;
                if (translator.MemberValues.TryGetValue(keyMember, out value))
                {
                    commandText.Append(value.ParameterName);
                }
                else
                {
                    // if no value is registered for the key member, it means it is an identity
                    // which can be retrieved using the scope_identity() function
                    if (identity)
                    {
                        // there can be only one server generated key
                        throw new NotSupportedException(string.Format("Server-generated keys are only supported for identity columns. More than one key column is marked as server generated in table '{0}'.", table.Name));
                    }

                    if (!IsValidIdentityColumnType(keyMember.TypeUsage))
                    {
                        throw new InvalidOperationException(string.Format(
                            "Server-generated keys are only supported for identity columns. Key column '{0}' has type '{1}', which is not a valid type for an identity column.",
                            keyMember.Name, keyMember.TypeUsage.ToString()));
                    }

                    commandText.Append("scope_identity()");
                    identity = true;
                }
            }
#else
            //
            // ##ORACLE
            // Wrap given CommandText for insert statement into anonymous PL/SQL block
            // and declare variable for each server-generated column at the beginning. Create
            // output cursor parameter to return server-generated columns.
            //
            // declare
            //  v1 type1, v2 type2  
            // begin
            //   insert ... returning c1, c2 into v1, v2;
            //   for :cursor open select v1, v2 from dual;
            // end;
            //

            EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;

            StringBuilder startBlock = new StringBuilder(50);

            startBlock.Append("declare\n");
            foreach (EdmMember keyMember in table.ElementType.KeyMembers)
            {
                startBlock.Append(GenerateMemberTSql(keyMember));
                startBlock.Append(" ");
                startBlock.Append(SqlGenerator.GetSqlPrimitiveType(providerManifest, sqlVersion, keyMember.TypeUsage));
                startBlock.Append(";\n");
            }
            startBlock.Append("begin\n");

            commandText.Insert(0, startBlock.ToString());

            //OracleParameter cursorParameter = translator.CreateParameter(OracleDbType.Cursor, ParameterDirection.Output);
            OracleParameter cursorParameter = translator.CreateParameter(OracleDbType.RefCursor, ParameterDirection.Output);
            string separator;

            commandText.Append("returning\n");
            separator = string.Empty;
            foreach (EdmMember keyMember in table.ElementType.KeyMembers)
            {
                commandText.Append(separator);
                commandText.Append(GenerateMemberTSql(keyMember));
                separator = ", ";
            }

            commandText.Append(" into\n");

            separator = string.Empty;
            foreach (EdmMember keyMember in table.ElementType.KeyMembers)
            {
                commandText.Append(separator);
                commandText.Append(GenerateMemberTSql(keyMember));
                separator = ", ";
            }

            commandText.Append(";\n");
            commandText.Append("open ");
            commandText.Append(cursorParameter.ParameterName);
            commandText.Append(" for select\n");

            separator = string.Empty;
            foreach (EdmMember keyMember in table.ElementType.KeyMembers)
            {
                commandText.Append(separator);
                commandText.Append(GenerateMemberTSql(keyMember));
                commandText.Append(" as ");
                commandText.Append(GenerateMemberTSql(keyMember));
                separator = ", ";
            }

            commandText.Append(" from dual;\n");
            commandText.Append("end;");
            return;
#endif
        }

        private static bool IsValidIdentityColumnType(TypeUsage typeUsage)
        {
            // SQL Server supports the following types for identity columns:
            // tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)

            // make sure it's a primitive type
            if (typeUsage.EdmType.BuiltInTypeKind != BuiltInTypeKind.PrimitiveType)
            {
                return false;
            }

            // check if this is a supported primitive type (compare by name)
            string typeName = typeUsage.EdmType.Name;

            // integer types
            if (typeName == "tinyint" || typeName == "smallint" ||
                typeName == "int" || typeName == "bigint")
            {
                return true;
            }

            // variable scale types (require scale = 0)
            if (typeName == "decimal" || typeName == "numeric")
            {
                Facet scaleFacet;
                return (typeUsage.Facets.TryGetValue(MetadataHelpers.ScaleFacetName,
                    false, out scaleFacet) && Convert.ToInt32(scaleFacet.Value, CultureInfo.InvariantCulture) == 0);
            }

            // type not in supported list
            return false;
        }

        /// <summary>
        /// Lightweight expression translator for DML expression trees, which have constrained
        /// scope and support.
        /// </summary>
        private class ExpressionTranslator : BasicExpressionVisitor
        {
            /// <summary>
            /// Initialize a new expression translator populating the given string builder
            /// with command text. Command text builder and command tree must not be null.
            /// </summary>
            /// <param name="commandText">Command text with which to populate commands</param>
            /// <param name="commandTree">Command tree generating SQL</param>
            /// <param name="preserveMemberValues">Indicates whether the translator should preserve
            /// member values while compiling t-SQL (only needed for server generation)</param>
            internal ExpressionTranslator(StringBuilder commandText, DbModificationCommandTree commandTree, 
                bool preserveMemberValues, EFOracleVersion version)
            {
                Debug.Assert(null != commandText);
                Debug.Assert(null != commandTree);
                _commandText = commandText;
                _commandTree = commandTree;
                _version = version;
                _parameters = new List<OracleParameter>();
                _memberValues = preserveMemberValues ? new Dictionary<EdmMember, OracleParameter>() :
                    null;
            }

            private readonly StringBuilder _commandText;
            private readonly DbModificationCommandTree _commandTree;
            private readonly List<OracleParameter> _parameters;
            private readonly Dictionary<EdmMember, OracleParameter> _memberValues;            
            private readonly EFOracleVersion _version;
            private int parameterNameCount = 0;

            internal List<OracleParameter> Parameters { get { return _parameters; } }
            internal Dictionary<EdmMember, OracleParameter> MemberValues { get { return _memberValues; } }

            internal OracleParameter CreateParameter(OracleDbType OracleDbType, ParameterDirection direction)
            {
                OracleParameter parameter = new OracleParameter();
                parameter.ParameterName = NextName();
                parameter.OracleDbType = OracleDbType;
                parameter.Direction = direction;
                
                _parameters.Add(parameter);
                return parameter;
            }

            internal OracleParameter CreateParameter(object value, TypeUsage type)
            {
                OracleParameter parameter = EFOracleOdpProviderServices.CreateOracleParameter(NextName(), type, ParameterMode.In, value, _version);
                _parameters.Add(parameter);
                return parameter;
            }

            private string NextName()
            {
                // ##ORACLE CONSIDER use of less naive alias generator
                string parameterName = string.Concat(":p", parameterNameCount.ToString(CultureInfo.InvariantCulture));
                parameterNameCount++;
                return parameterName;
            }

            public override void Visit(DbAndExpression expression)
            {
                VisitBinary(expression, " and ");
            }

            public override void Visit(DbOrExpression expression)
            {
                VisitBinary(expression, " or ");
            }

            public override void Visit(DbComparisonExpression expression)
            {
                Debug.Assert(expression.ExpressionKind == DbExpressionKind.Equals,
                    "only equals comparison expressions are produced in DML command trees in V1");

                VisitBinary(expression, " = ");

                RegisterMemberValue(expression.Left, expression.Right);
            }

            /// <summary>
            /// Call this method to register a property value pair so the translator "remembers"
            /// the values for members of the row being modified. These values can then be used
            /// to form a predicate for server-generation (based on the key of the row)
            /// </summary>
            /// <param name="propertyExpression">DbExpression containing the column reference (property expression).</param>
            /// <param name="value">DbExpression containing the value of the column.</param>
            internal void RegisterMemberValue(DbExpression propertyExpression, DbExpression value)
            {
                if (null != _memberValues)
                {
                    // register the value for this property
                    Debug.Assert(propertyExpression.ExpressionKind == DbExpressionKind.Property,
                        "DML predicates and setters must be of the form property = value");

                    // get name of left property 
                    EdmMember property = ((DbPropertyExpression)propertyExpression).Property;

                    // don't track null values
                    if (value.ExpressionKind != DbExpressionKind.Null)
                    {
                        Debug.Assert(value.ExpressionKind == DbExpressionKind.Constant,
                            "value must either constant or null");
                        // retrieve the last parameter added (which describes the parameter)
                        _memberValues[property] = _parameters[_parameters.Count - 1];
                    }
                }
            }

            public override void Visit(DbIsNullExpression expression)
            {
                expression.Argument.Accept(this);
                _commandText.Append(" is null");
            }

            public override void Visit(DbNotExpression expression)
            {
                _commandText.Append("not (");
                expression.Accept(this);
                _commandText.Append(")");
            }

            public override void Visit(DbConstantExpression expression)
            {
                OracleParameter parameter = CreateParameter(expression.Value, expression.ResultType);
                _commandText.Append(parameter.ParameterName);
            }

            public override void Visit(DbScanExpression expression)
            {
                string definingQuery = MetadataHelpers.GetMetadataProperty<string>(expression.Target,
                    MetadataHelpers.DefiningQueryMetadata);

                // we know we won't hit this code unless there is no function defined for this
                // ModificationOperation, so if this EntitySet is using a DefiningQuery, instead
                // of a table, that is an error
                if (definingQuery != null)
                {
                    string missingCudElement;
                    if (_commandTree is DbDeleteCommandTree)
                    {
                        missingCudElement = "DeleteFunction";
                    }
                    else if (_commandTree is DbInsertCommandTree)
                    {
                        missingCudElement = "InsertFunction";
                    }
                    else
                    {
                        Debug.Assert(_commandTree is DbUpdateCommandTree, "did you add a new option?");
                        missingCudElement = "UpdateFunction";
                    }
                    throw new InvalidOperationException(string.Format(
                        "Unable to update the EntitySet '{0}' because it has a DefiningQuery and " +
                        "no <{1}> element exists in the <{2}> element to support the current operation.",
                        expression.Target.Name, missingCudElement,
                        "ModificationFunctionMapping"));
                }

                _commandText.Append(SqlGenerator.GetTargetTSql(expression.Target));
            }

            public override void Visit(DbPropertyExpression expression)
            {
                _commandText.Append(GenerateMemberTSql(expression.Property));
            }

            public override void Visit(DbNullExpression expression)
            {
                _commandText.Append("null");
            }

            public override void Visit(DbNewInstanceExpression expression)
            {
                // assumes all arguments are self-describing (no need to use aliases
                // because no renames are ever used in the projection)
                bool first = true;
                foreach (DbExpression argument in expression.Arguments)
                {
                    if (first) { first = false; }
                    else { _commandText.Append(", "); }
                    argument.Accept(this);
                }
            }

            private void VisitBinary(DbBinaryExpression expression, string separator)
            {
                _commandText.Append("(");
                expression.Left.Accept(this);
                _commandText.Append(separator);
                expression.Right.Accept(this);
                _commandText.Append(")");
            }

        }
    }
}

